#coding=utf-8

import pymysql
import os
import shutil
import oss2
import base64
import json
import uuid
import requests


# 连接MySQL数据库
def connect_to_mysql():
    try:
        # conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='dbtest2',charset='utf8', port=3308)
        # 本地 docker 数据库
        conn = pymysql.connect(host='127.0.0.1', user='root', password='abc123', db='dbtest', charset='utf8', port=13306)
        return conn
    except Exception as e:
        print("连接MySQL失败: ", e)


# 从MySQL读取数据
def fetch_data(conn, table_name, column_name, value):
    try:
        with conn.cursor() as cursor:
            sql = f"SELECT id, robot_talk FROM {table_name} WHERE {column_name} = %s"
            cursor.execute(sql, (value,))
            result = cursor.fetchall()
            return result if result else None
    except Exception as e:
        print("读取数据失败: ", e)


# 将文本转换为语音
def generate_speech(text):
    # 填写平台申请的appid, access_token以及cluster
    appid = "8166351805"
    access_token = "SlcalXtOvJZTd1sMCOEk6eEUD_P8UkY0"
    cluster = "3ICc2THCT6r0h257Np2hDwpCo5FQNxBh"

    voice_type = "BV009_streaming"  # 知性女声 1
    # voice_type = "BV007_streaming"  # 灿灿2.0 2
    # voice_type = "BV008_streaming"  # 亲切男声 3
    host = "openspeech.bytedance.com"
    api_url = f"https://{host}/api/v1/tts"

    header = {"Authorization": f"Bearer;{access_token}"}

    request_json = {
        "app": {
            "appid": appid,
            "token": "access_token",
            "cluster": "volcano_tts"
        },
        "user": {
            "uid": "2101040738"
        },
        "audio": {
            "voice_type": voice_type,
            "encoding": "mp3",
            "speed_ratio": 1.0,
            "volume_ratio": 1.0,
            "pitch_ratio": 1.0,
        },
        "request": {
            "reqid": str(uuid.uuid4()),
            "text": text,
            "text_type": "plain",
            "operation": "query",
            "with_frontend": 1,
            "frontend_type": "unitTson"

        }
    }
    try:
        resp = requests.post(api_url, json.dumps(request_json), headers=header)
        print(f"resp body: \n{resp.json()}")
        if "data" in resp.json():
            # file_name = str(uuid.uuid4())+".wav"
            file_name = str(uuid.uuid4())+".mp3"
            data = resp.json()["data"]
            file_to_save = open(file_name, "wb")
            file_to_save.write(base64.b64decode(data))
            return file_name
    except Exception as e:
        e.with_traceback()


# 上传文件到阿里云OSS
def upload_to_oss(file_name):
    oss_file_name = 'aiApplet/benz-poc-615/' + file_name
    access_key_id = os.getenv('OSS_TEST_ACCESS_KEY_ID', 'LTAIYXF7tGOgOfrX')
    access_key_secret = os.getenv('OSS_TEST_ACCESS_KEY_SECRET', '83Z90MMp6PgJoJgOzDvFtYnpGHNxX5')
    bucket_name = os.getenv('OSS_TEST_BUCKET', 'ai-dl')
    endpoint = os.getenv('OSS_TEST_ENDPOINT', 'http://oss-cn-hangzhou.aliyuncs.com')
    domain = 'https://ai-dl.oss-cn-hangzhou.aliyuncs.com/'

    # 从环境变量中获取访问凭证。运行本代码示例之前，请确保已设置环境变量OSS_ACCESS_KEY_ID和OSS_ACCESS_KEY_SECRET。
    auth = oss2.Auth(access_key_id, access_key_secret)
    # yourEndpoint填写Bucket所在地域对应的Endpoint。以华东1（杭州）为例，Endpoint填写为https://oss-cn-hangzhou.aliyuncs.com。
    # 填写Bucket名称。
    bucket = oss2.Bucket(auth, endpoint, bucket_name)

    # 必须以二进制的方式打开文件。
    # 填写本地文件的完整路径。如果未指定本地路径，则默认从示例程序所属项目对应本地路径中上传文件。
    with open(file_name, 'rb') as fileobj:
        # Seek方法用于指定从第1000个字节位置开始读写。上传时会从您指定的第1000个字节位置开始上传，直到文件结束。
        fileobj.seek(1000, os.SEEK_SET)
        # Tell方法用于返回当前位置。
        current = fileobj.tell()
        # 填写Object完整路径。Object完整路径中不能包含Bucket名称。
        bucket.put_object(oss_file_name, fileobj)
    return domain + oss_file_name


# 更新URL到数据库
def update_url_in_db(conn, value, url):
    try:
        with conn.cursor() as cursor:
            sql = f"UPDATE ai_training_script_detail SET robot_talk_wav_url = %s WHERE id = %s"
            cursor.execute(sql, (url, value))
            conn.commit()
            print("URL更新成功")
    except Exception as e:
        print("更新URL失败: ", e)


# 主函数
def main():
    conn = connect_to_mysql()
    if conn:
        try:
            # 假设我们根据某个条件查询数据，例如id为1
            cursor = fetch_data(conn, 'ai_training_script_detail', 'script_id', 12)
            print(cursor)
            for row in cursor:
                print(row[0], row[1])
                speech = generate_speech(row[1])
                file_url = upload_to_oss(speech)
                print(file_url)
                if file_url:
                    update_url_in_db(conn, row[0], file_url)
        finally:
            conn.close()


def main1(speech_text):
    speech = generate_speech(speech_text)
    file_url = upload_to_oss(speech)
    print(file_url)


if __name__ == "__main__":
    # main()
    main1("感谢您的参与，本场景考试结束")
    # list = [
    #
    # '能介绍一下这台车的音响吗？',
    # ]
    # for i in list:
    #     print(i)
    #     main1(i)
